In [1]:
# Questions 1
In [2]:
import sqlite3
conn = sqlite3.connect('movie.db')
cur = conn.cursor()
In [3]:
cur.execute('''SELECT film.title
FROM film, person, participation
WHERE film.genre LIKE '%Thriller%'
AND film.id = participation.film
AND person.id = participation.person
AND participation.function = "director"
AND person.name= "Spielberg"
AND person.firstname = "Steven" ''')
for row in cur.fetchall():
print(row[0])
In [4]:
cur.execute('''SELECT DISTINCT person.firstname, person.name
FROM person, participation
WHERE person.id = participation.person
AND participation.person IN
(SELECT participation.person
FROM participation
GROUP BY participation.person
HAVING count(*)>20);''')
cur.execute('''SELECT person.firstname, person.name
FROM (person JOIN participation ON participation.person=person.ID)
GROUP BY person.ID
HAVING COUNT(participation.film) > 20;''')
# Both statements work.
for row in cur.fetchall():
print(row[0], row[1])
In [5]:
cur.execute('''SELECT show.date, cinema.name, cinema.city
FROM show, film, cinema
WHERE show.film = film.id
AND show.cinema = cinema.id
AND film.title="Alice in Wonderland";''')
for row in cur.fetchall():
print(row[0], row[1], row[2])
In [6]:
cur.execute('''SELECT p.firstname, p.name, f.title
FROM (((person p INNER JOIN participation par
ON p.ID=par.person AND par.function="director") INNER JOIN film f
ON par.film=f.ID) INNER JOIN participation par2
ON p.ID=par2.person AND par2.film=par.film AND par2.function="actor")
ORDER BY p.name;''')
'''
Some of the results seem to be counter intuitive as actors in animation movies
or severall people acting and directing in the same movie. But this is due to
co-directors and the voice cast of animation movies
'''
for row in cur.fetchall()[:20]:
print(row[0], row[1], row[2])
In [12]:
cur.execute('''SELECT DISTINCT c.name, c.city
FROM (cinema c INNER JOIN show s
ON c.ID=s.cinema)
WHERE s.film IN
(SELECT f.ID
FROM (film f INNER JOIN participation par
ON f.ID= par.film)
WHERE par.person=
(SELECT p.ID
FROM person p
WHERE p.name="Winslet" AND p.firstname="Kate"))
;''')
cur.execute('''SELECT DISTINCT c.name, c.city
FROM (((cinema c JOIN show s ON c.ID=s.cinema)
JOIN participation par ON s.film = par.film)
JOIN person p ON p.ID=person)
WHERE p.name="Winslet" AND p.firstname="Kate"
;''')
for row in cur.fetchall()[:20]:
print(row)
In [16]:
cur.execute('''SELECT DISTINCT f.title
FROM ((film f INNER JOIN participation par
ON f.ID = par.film AND par.function='director') INNER JOIN participation par1
ON f.ID = par1.film AND par1.function='director' AND par.person IS NOT par1.person)
;''')
cur.execute('''SELECT f.title
FROM (film f JOIN participation par ON f.ID = par.film)
WHERE par.function='director'
GROUP BY ID
HAVING COUNT(*) > 1
ORDER BY f.title asc
;''')
for row in cur.fetchall()[:20]:
print(*row)
In [ ]:
cur.execute('''SELECT f.title
FROM (film f JOIN show s
ON f.ID=s.film)
WHERE s.date > '2015-05-30'
;''')
'''The dates have been assigned randomly between 1980-01-01 and 2016-01-01 during database creation. '''
for row in cur.fetchall()[:20]:
print(*row)
In [ ]:
cur.execute('''SELECT p.firstname, p.name
FROM person p
EXCEPT
SELECT p.firstname, p.name
FROM (person p JOIN participation par
ON p.ID=par.person)
ORDER BY p.name, p.firstname
;''')
# It seems that for severall actors no participation record was written
for row in cur.fetchall()[:20]:
print(*row)
In [ ]:
cur.execute('''SELECT p.firstname, p.name
FROM person p
WHERE p.ID NOT IN (SELECT par.person
FROM participation par)
ORDER BY p.name, p.firstname
;''')
for row in cur.fetchall()[:20]:
print(*row)
In [ ]:
cur.execute('''SELECT p.firstname, p.name
FROM person p
WHERE p.ID IN (
SELECT x.person
FROM (
(SELECT *
FROM (film f JOIN participation par
ON f.ID=par.film)
WHERE par.function="director" ) as x
JOIN
(SELECT *
FROM (film f1 JOIN participation par1
ON f1.ID=par1.film)
WHERE par1.function="director" ) as y
ON x.year=y.year
AND x.person = y.person
AND x.film <> y.film
))
;''')
for row in cur.fetchall()[:100]:
print(*row)
In [ ]:
cur.execute('''SELECT f.year, f.title
FROM (( film f JOIN participation par
ON f.ID=par.film) JOIN person p ON p.ID=par.person)
WHERE p.name = "Donner"
AND p.firstname = "Richard"
AND par.function='director'
Order By f.year
;''')
# Just to see which movies where made in the same year
for row in cur.fetchall()[:100]:
print(*row)
In [ ]:
cur.execute('''SELECT DISTINCT p.firstname, p.name
FROM person p JOIN person p1
ON p.name = p1.name
AND p.firstname = p1.firstname
AND p.ID <> p1.ID
ORDER BY p.name, p.firstname
;''')
# Just to see which movies where made in the same year
for row in cur.fetchall()[:20]:
print(*row)
What is the meaning of the following SQL queries over the film schema. Provid the corresponding realational algebra expressions.
In [ ]:
cur.execute('''SELECT DISTINCT p.firstname, p.name
FROM (((person p JOIN participation par
ON p.ID=par.person) JOIN film f
ON par.film=f.ID) JOIN show s
ON f.ID=s.film)
WHERE s.date<"2017-01-01"
;''')
# Just to see which movies where made in the same year
for row in cur.fetchall()[:20]:
print(*row)
In [ ]:
cur.execute('''SELECT DISTINCT p.firstname, p.name
FROM person p
WHERE EXISTS (SELECT par.person
FROM (participation par JOIN show s
ON s.film=par.film )
WHERE s.date<"2017-01-01")
;''')
# Just to see which movies where made in the same year
for row in cur.fetchall()[:20]:
print(*row)